package org.starapp.generator;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.starapp.utils.ArrayUtils;
import org.starapp.utils.StringUtils;

public class DBConnection {
	private Connection conn;
	private Statement stat = null;
	private DatabaseMetaData meta = null;
	private ResultSetMetaData rsme = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private String driver;
	private String jdbcUrl;
	private String user;
	private String password;


	public DBConnection(String driver, String jdbcUrl, String user,
			String password) {
		this.driver = driver;
		this.jdbcUrl = jdbcUrl;
		this.user = user;
		this.password = password;
	}

	public void open() {
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(jdbcUrl, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void close() {
		try {
		} catch (Exception e) {
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					conn.close();
					conn = null;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private void realase() {
		try {
			if (stat != null) {
				stat.close();
				stat = null;
			}
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (ps != null) {
				ps.close();
				ps = null;
			}
			if (rsme != null) {
				rsme = null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void executeBatch(List<String> sqlList) {

		try {
			stat = conn.createStatement();
			for (String dllsql : sqlList) {
				stat.addBatch(dllsql);
			}
			stat.executeBatch();

		} catch (Exception e) {
		} finally {
			realase();
		}
	}

	public void execute(String sql) {
		try {
			stat = conn.createStatement();
			stat.execute(sql);

		} catch (Exception e) {
		} finally {
			realase();
		}
	}

	public void executeUpate(String sql) {
		try {
			stat = conn.createStatement();
			stat.executeUpdate(sql);
		} catch (Exception e) {
		} finally {
			realase();
		}
	}
	
	/**
	 * Get All Tables Info
	 * 
	 * @param tableNames
	 * @return
	 */
	public List<Table> getTableDataWithPrefix(String prefix) {
		return this.getTableData(prefix,null);
	}
	
	/**
	 * Get All Tables Info
	 * 
	 * @param tableNames
	 * @return
	 */
	public List<Table> getAllTableInfo() {
		return this.getTableData(null,null);
	}

	/**
	 * Get Some Tables Info
	 * 
	 * @param tableNames
	 * @return
	 */
	public List<Table> getSomeTableInfo(String[] tableNames) {
		return this.getTableData(null, tableNames);
	}
	/**
	 * Get All Tables Info
	 * 
	 * @param tableNames
	 * @return
	 */
	public List<Table> getTableData(String prefix,String[] tableNames) {
		List<Table> list = null;
		//如果为空，则取所有
		if(ArrayUtils.isEmpty(tableNames)){
			tableNames = this.getTables();
		}
		if (ArrayUtils.isEmpty(tableNames)) {
			return null;
		}
		for (String tableName : tableNames) {
			if(!StringUtils.isEmpty(prefix)){
				if(tableName.startsWith(prefix)){
					Table table = this.getTableInfo(tableName);
					if (list == null && table != null) {
						list = new ArrayList<Table>();
					}
					list.add(table);
				}
			}else{
				Table table = this.getTableInfo(tableName);
				if (list == null && table != null) {
					list = new ArrayList<Table>();
				}
				list.add(table);
			}

		}
		// 处理以下
		TableUtils.convertTableData(list);

		return list;
	}

	/**
	 * Get Table Info
	 * 
	 * @param tableName
	 * @return
	 */
	public Table getTableInfo(String tableName) {
		Table table = null;
		try {
			meta = conn.getMetaData();
			// get Fields
			List<TableField> fields = this.getTableFields(tableName);
			if (fields != null) {
				table = new Table();
				table.setFields(fields);
				table.setTableName(tableName);
				table.setClassName(StringUtils.convent2javaFormat(ResourceUtils.getTablePrefix(),table
						.getTableName()));
				table.setClassAttrName(StringUtils.convent2javaLowerFormat(ResourceUtils.getTablePrefix(),table
						.getTableName()));
			}
			if (table == null) {
				return null;
			}
			// get Primary keys
			List<PrimaryKey> primaryKeys = this.getPrimaryKeys(tableName);
			if (primaryKeys != null) {
				table.setPrimaryKeys(primaryKeys);
			}
			// get Foreign keys
			List<ForeignKey> foreignKeys = this.getForeignKeys(tableName);
			if (foreignKeys != null) {
				table.setForeignKeys(foreignKeys);
			}

		} catch (Exception e) {
		} finally {
			realase();
		}
		return table;
	}

	public String[] getTables() {
		List<String> list = null;
		try {
			meta = conn.getMetaData();
			rs = meta.getTables(conn.getCatalog(), null, null,
					new String[] { "TABLE" });
			if (rs != null) {
				list = new ArrayList<String>();
			}
			while (rs.next()) {
				list.add(rs.getString("TABLE_NAME"));
			}
		} catch (Exception e) {
		} finally {
			realase();
		}
		return ArrayUtils.list2ArrayString(list);
	}

	/**
	 * Get All Fields
	 * 
	 * @param tableName
	 * @return
	 */
	public List<TableField> getTableFields(String tableName) {
		List<TableField> list = null;
		TableField field = null;
		try {
			ps = conn.prepareStatement("select * from " + tableName);
			rs = ps.executeQuery();
			rsme = rs.getMetaData();
			int columnCount = rsme.getColumnCount();
			if (columnCount > 0) {
				list = new ArrayList<TableField>();
			}

			for (int i = 1; i <= columnCount; i++) {
				field = new TableField();
				field.setCatalogName(rsme.getCatalogName(i));
				field.setColumnClassName(rsme.getColumnClassName(i));
				field.setColumnCount(rsme.getColumnCount());
				field.setColumnDisplaySize(rsme.getColumnDisplaySize(i));
				field.setColumnLabel(rsme.getColumnLabel(i));
				field.setColumnName(rsme.getColumnName(i));
				field.setColumnType(rsme.getColumnType(i));
				field.setColumnTypeName(rsme.getColumnTypeName(i));
				field.setPrecision(rsme.getPrecision(i));
				field.setScale(rsme.getScale(i));
				field.setSchemaName(rsme.getSchemaName(i));
				field.setTableName(rsme.getTableName(i));
				field.setAutoIncrement(rsme.isAutoIncrement(i));
				field.setCaseSensitive(rsme.isCaseSensitive(i));
				field.setCurrency(rsme.isCurrency(i));
				field.setDefinitelyWritable(rsme.isDefinitelyWritable(i));
				field.setIsNullable(rsme.isNullable(i));
				field.setReadOnly(rsme.isReadOnly(i));
				field.setSearchable(rsme.isSearchable(i));
				field.setSigned(rsme.isSigned(i));
				field.setWritable(rsme.isWritable(i));

				field.setJavaColumnName(StringUtils.convent2javaFormat(field
						.getColumnName()));
				field.setJavaColumnTypeName(DBTypeEnum.getType(field
						.getColumnTypeName()));
				field.setJavaColumnAttrName(StringUtils
						.convent2javaLowerFormat(field.getColumnName()));

				list.add(field);

			}
		} catch (Exception e) {
		} finally {
			realase();
		}
		return list;
	}

	/**
	 * Get Primary keys
	 * 
	 * @param tableName
	 * @return
	 */
	public List<PrimaryKey> getPrimaryKeys(String tableName) {
		List<PrimaryKey> list = null;
		PrimaryKey primaryKey = null;
		try {
			meta = conn.getMetaData();
			rs = meta.getPrimaryKeys(conn.getCatalog(), null, tableName);
			if (rs != null) {
				list = new ArrayList<PrimaryKey>();
			}
			while (rs.next()) {
				primaryKey = new PrimaryKey();
				primaryKey.setColumnName(rs.getString("COLUMN_NAME"));
				primaryKey.setKeySeq(rs.getShort("KEY_SEQ"));
				primaryKey.setPkName(rs.getString("PK_NAME"));
				primaryKey.setTableCat(rs.getString("TABLE_CAT"));
				primaryKey.setTableName(rs.getString("TABLE_NAME"));
				primaryKey.setTableShem(rs.getString("TABLE_SCHEM"));

				primaryKey.setClassAttrName(StringUtils
						.convent2javaLowerFormat(ResourceUtils.getTablePrefix(),primaryKey.getTableName()));
				primaryKey.setClassName(StringUtils
						.convent2javaFormat(ResourceUtils.getTablePrefix(),primaryKey.getTableName()));

				primaryKey.setJavaColumnAttrName(StringUtils
						.convent2javaLowerFormat(primaryKey.getColumnName()));
				primaryKey.setJavaColumnName(StringUtils
						.convent2javaFormat(primaryKey.getColumnName()));

				list.add(primaryKey);
			}
		} catch (Exception e) {
		} finally {
			realase();
		}
		return list;
	}

	/**
	 * Get Foreign keys
	 * 
	 * @param tableName
	 * @return
	 */
	public List<ForeignKey> getForeignKeys(String tableName) {
		List<ForeignKey> list = null;
		ForeignKey foreignKey = null;
		try {
			meta = conn.getMetaData();
			rs = meta.getImportedKeys(conn.getCatalog(), null, tableName);
			if (rs != null) {
				list = new ArrayList<ForeignKey>();
			}
			while (rs.next()) {
				foreignKey = new ForeignKey();
				foreignKey.setDeferrability(rs.getShort("DEFERRABILITY"));
				foreignKey.setDeleteRule(rs.getShort("DELETE_RULE"));
				foreignKey.setFkColumnName(rs.getString("FKCOLUMN_NAME"));
				foreignKey.setFkName(rs.getString("FK_NAME"));
				foreignKey.setFkTableCat(rs.getString("FKTABLE_CAT"));
				foreignKey.setFkTableName(rs.getString("FKTABLE_NAME"));
				foreignKey.setFkTableSchem(rs.getString("FKTABLE_SCHEM"));
				foreignKey.setKeySeq(rs.getShort("KEY_SEQ"));
				foreignKey.setPkColumnName(rs.getString("PKCOLUMN_NAME"));
				foreignKey.setPkName(rs.getString("PK_NAME"));
				foreignKey.setPkTableCat(rs.getString("PKTABLE_CAT"));
				foreignKey.setPkTableName(rs.getString("PKTABLE_NAME"));
				foreignKey.setPkTableShem(rs.getString("PKTABLE_SCHEM"));
				foreignKey.setUpdateRule(rs.getShort("UPDATE_RULE"));

				//
				foreignKey.setFkJavaClassAttrName(StringUtils
						.convent2javaLowerFormat(ResourceUtils.getTablePrefix(),foreignKey.getFkTableName()));
				foreignKey.setFkJavaClassName(StringUtils
						.convent2javaFormat(ResourceUtils.getTablePrefix(),foreignKey.getFkTableName()));
				
				foreignKey.setFkJavaColumnAttrName(StringUtils
						.convent2javaLowerFormat(foreignKey.getFkColumnName()));
				foreignKey.setFkJavaColumnName(StringUtils
						.convent2javaFormat(foreignKey.getFkColumnName()));

				foreignKey.setPkJavaClassAttrName(StringUtils
						.convent2javaLowerFormat(ResourceUtils.getTablePrefix(),foreignKey.getPkTableName()));
				foreignKey.setPkJavaClassName(StringUtils
						.convent2javaFormat(ResourceUtils.getTablePrefix(),foreignKey.getPkTableName()));
				
				foreignKey.setPkJavaColumnAttrName(StringUtils
						.convent2javaLowerFormat(foreignKey.getPkColumnName()));
				foreignKey.setPkJavaColumnName(StringUtils
						.convent2javaFormat(foreignKey.getPkColumnName()));
				list.add(foreignKey);
			}
		} catch (Exception e) {
		} finally {
			realase();
		}
		return list;
	}

}
